﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.Practices.EnterpriseLibrary.Data;
using System.Data.Common;
using Dapper;
using System.Data;
using System.Text.RegularExpressions;
using System.Data.OracleClient;

namespace _180FrameWork.DataBaseHelper.OracleExtend
{
    public class OracleJintDB : IJintDB
    {
        private Database _db;

        private DbConnection _Conn;

        private DbConnection Conn
        {
            get
            {
                if (_Conn == null)
                    _Conn = this._db.CreateConnection();
                return this._Conn;
            }
        }

        private string _paramChar = ":";

        public OracleJintDB(string DBConnectionString)
        {
            _db = new Microsoft.Practices.EnterpriseLibrary.Data.Oracle.OracleDatabase(DBConnectionString);
        }
        #region Dapper

        /// <summary>
        /// 查询单个
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public dynamic Find(string sql)
        {
            return this.Conn.Query(sql).FirstOrDefault();
        }
        /// <summary>
        /// 查询单个
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="param"></param>
        /// <returns></returns>
        public dynamic Find(string sql, IDictionary<string, object> param)
        {
            return this.Conn.Query(sql, GetParams(sql, param)).FirstOrDefault();
        }
        /// <summary>
        /// 查询单个
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="param"></param>
        /// <param name="tran"></param>
        /// <returns></returns>
        public dynamic Find(string sql, IDictionary<string, object> param, IDbTransaction tran)
        {
            return this.Conn.Query(sql, GetParams(sql, param), tran).FirstOrDefault();
        }
        /// <summary>
        /// 查询列表
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public IList<dynamic> Query(string sql)
        {
            return this.Conn.Query(sql).ToList();
        }
        /// <summary>
        /// 查询列表
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="param"></param>
        /// <returns></returns>
        public IList<dynamic> Query(string sql, IDictionary<string, object> param)
        {
            return this.Conn.Query(sql, GetParams(sql, param)).ToList();
        }
        /// <summary>
        /// 查询列表
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="param"></param>
        /// <param name="tran"></param>
        /// <returns></returns>
        public IList<dynamic> Query(string sql, IDictionary<string, object> param, IDbTransaction tran)
        {
            return this.Conn.Query(sql, GetParams(sql, param), tran).ToList();
        }
        /// <summary>
        /// 查询分页
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public PageData<dynamic> Page(string sql)
        {
            return Page(sql, new Dictionary<string, object>());
        }
        /// <summary>
        /// 查询分页
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="param"></param>
        /// <param name="page"></param>
        /// <param name="rows"></param>
        /// <param name="sort"></param>
        /// <param name="order"></param>
        /// <returns></returns>
        public PageData<dynamic> Page(string sql, IDictionary<string, object> param)
        {
            int page = 1; int rows = 20; string sort = ""; string order = ""; int total = 0; string SUM = "";
            foreach (KeyValuePair<string, object> item in param)
            {
                switch (item.Key)
                {
                    case "page":
                        page = Convert.ToInt32(item.Value);
                        break;
                    case "rows":
                        rows = Convert.ToInt32(item.Value);
                        break;
                    case "sort":
                        sort = Convert.ToString(item.Value);
                        break;
                    case "order":
                        order = Convert.ToString(item.Value);
                        break;
                    case "total":
                        total = Convert.ToInt32(item.Value);
                        break;
                    case "sum":
                        SUM = Convert.ToString(item.Value);
                        break;
                    //default:
                    //    dp.Add(item.Key, item.Value);
                    //    break;
                }
            }
            var dp = GetParams(sql, param);
            var p = new PageData<dynamic>();
            if (total != 0)
            {
                p.total = total;
            }
            else
            {
                string strTotalSql = "select count(*) from(" + sql + ") t";
                p.total = this.Conn.ExecuteScalar<int>(strTotalSql, dp);
                if (p.total == 0)
                    p.total = 1;
            } 
            p.totalPages = (p.total / rows) + (p.total % rows > 0 ? 1 : 0);
            //汇总
            if (SUM != "")
            {
                string strSumSql = "select " + SUM + " from(" + sql + ") t";
                p.footer = this.Conn.Query(strSumSql, dp).ToList();
            }
            //排序
            string orderby = "";
            if (sort != "")
            {
                orderby = " order by " + sort;
                if (order != "")
                {
                    orderby += " " + order;
                }
            }
            //获取分页数据
            int beginrow = rows * (page - 1) + 1;
            int endrow = rows * page;
            dp.Add("beginrow", beginrow);
            dp.Add("endrow", endrow);
            string strDataSql = "select c.* from(select t.*,ROWNUM AS RN from(select x.* from(" + sql + ")x " + orderby + " )t where rownum<=:ENDROW)c WHERE C.RN>=:BEGINROW ";
            p.rows = this.Conn.Query(strDataSql, dp).ToList();
            return p;
        }
        /*
      /// <summary>
      /// 查询表格
      /// </summary>
      /// <param name="sql"></param>
      /// <returns></returns>
      public GridData<dynamic> Grid(string sql) { return Grid(sql, new Dictionary<string, object>()); }
      /// <summary>
      /// 查询表格
      /// </summary>
      /// <param name="sql"></param>
      /// <param name="param"></param>
      /// <returns></returns>
      public GridData<dynamic> Grid(string sql, IDictionary<string, object> param)
      {
          string sort = ""; string order = ""; string SUM = "";
          foreach (KeyValuePair<string, object> item in param)
          {
              switch (item.Key)
              {                   
                  case "sort":
                      sort = Convert.ToString(item.Value);
                      break;
                  case "order":
                      order = Convert.ToString(item.Value);
                      break;              
                  case "sum":
                      SUM = Convert.ToString(item.Value);
                      break;
                  //default:
                  //    dp.Add(item.Key, item.Value);
                  //    break;
              }
          }
          var dp = GetParams(sql, param);
          var p = new GridData<dynamic>();
            
          //汇总
          if (SUM != "")
          {
              string strSumSql = "select " + SUM + " from(" + sql + ") t";
              p.footer = this.Conn.Query(strSumSql, dp).ToList();
          }
          //排序
          string orderby = "";
          if (sort != "")
          {
              orderby = " order by " + sort;
              if (order != "")
              {
                  orderby += " " + order;
              }
          }
          p.rows = this.Conn.Query(sql, dp).ToList();
          return p;
      }
      
      /// <summary>
      /// 查找标准数据结构
      /// </summary>
      /// <param name="sql"></param>
      /// <returns></returns>
      public DataTable QueryDataTable(string sql)
      {
          return QueryDataTable(sql, null);
      }
      /// <summary>
      /// 查找标准数据结构
      /// </summary>
      /// <param name="sql"></param>
      /// <param name="param"></param>
      /// <returns></returns>
      public DataTable QueryDataTable(string sql, IDictionary<string, object> param)
      {
          DbCommand command = _db.GetSqlStringCommand(sql);
          if (param != null)
          {
              foreach (string strPara in param.Keys)
              {
                  DbParameter para = command.CreateParameter();
                  para.ParameterName = strPara;
                  if (param[strPara] != null)
                      para.Value = param[strPara];
                  else
                      para.Value = DBNull.Value;
                  command.Parameters.Add(para);
              }
          }
          DataSet ds = _db.ExecuteDataSet(command);
          return ds.Tables[0];
      }
       */ 
        /// <summary>
        /// 执行语句
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public int Exec(string sql)
        {
            return this.Conn.Execute(sql);
        }
        /// <summary>
        /// 执行语句
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="param"></param>
        /// <returns></returns>
        public int Exec(string sql, IDictionary<string, object> param)
        {
            return this.Conn.Execute(sql, GetParams(sql, param));
        }
        /// <summary>
        /// 执行语句
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="param"></param>
        /// <param name="tran"></param>
        /// <returns></returns>
        public int Exec(string sql, IDictionary<string, object> param, IDbTransaction tran)
        {
            return this.Conn.Execute(sql, GetParams(sql, param), tran);
        }

       
        /// <summary>
        /// 存储过程
        /// </summary>
        /// <param name="spName"></param>
        /// <param name="param"></param>
        public IDictionary<string, object> Sp(string spName, IDictionary<string, object>[] param)
        {
            IDictionary<string, object> result = new Dictionary<string, object>();
            var p = GetOracleDynamicParameters(param);
            int listResultNumber = 0;
            foreach (var item in p.OracleParameters)
            {
                if (item.OracleType == OracleType.Cursor)
                {
                    listResultNumber++;
                }
            }
            if (listResultNumber == 0)
            {
                this.Conn.Execute(spName, p, null, null, CommandType.StoredProcedure);
                foreach (var item in p.OracleParameters)
                {
                    if (item.Direction == ParameterDirection.Output || item.Direction == ParameterDirection.InputOutput)
                    {
                        result.Add(item.ParameterName, item.Value);
                    }
                }
            }
            else
            {
                var gr = this.Conn.QueryMultiple(spName, p, null, null, CommandType.StoredProcedure);
                foreach (var item in p.OracleParameters)
                {

                    if (item.Direction == ParameterDirection.Output || item.Direction == ParameterDirection.InputOutput)
                    {
                        if (item.OracleType == OracleType.Cursor)
                        {
                            result.Add(item.ParameterName, gr.Read().ToList());
                        }
                        else
                        {
                            result.Add(item.ParameterName, item.Value);
                        }
                    }
                }
            }
            return result;
        }        
        
        private OracleDynamicParameters GetOracleDynamicParameters(IDictionary<string, object>[] param)
        {
            OracleDynamicParameters p = new OracleDynamicParameters();
            foreach (var item in param)
            {
                string pn = item.ContainsKey("pn") ? item["pn"].ToString() : "";
                if (pn != "")
                {
                    object pv = item.ContainsKey("pv") ? item["pv"] : null;
                    OracleType pt = item.ContainsKey("pt") ? GetOracleType(item["pt"].ToString()) : OracleType.VarChar;
                    int ps = item.ContainsKey("ps") ? Convert.ToInt32(item["ps"]) : 0;
                    ParameterDirection pd = item.ContainsKey("pd") ? GetParameterDirection(item["pd"].ToString()) : ParameterDirection.Input;
                    p.Add(pn, pv, pt, ps, pd);
                }
            }
            return p;
        }
        private OracleType GetOracleType(string pt)
        {
            OracleType result = OracleType.VarChar;
            switch (pt.ToUpper())
            {
                case "BFILE": result = OracleType.BFile; break;
                case "BLOB": result = OracleType.Blob; break;
                case "CHAR": result = OracleType.Char; break;
                case "CLOB": result = OracleType.Clob; break;
                case "CURSOR": result = OracleType.Cursor; break;
                case "DATETIME": result = OracleType.DateTime; break;
                case "INTERVALDAYTOSECOND": result = OracleType.IntervalDayToSecond; break;
                case "INTERVALYEARTOMONTH": result = OracleType.IntervalYearToMonth; break;
                case "LONGRAW": result = OracleType.LongRaw; break;
                case "LONGVARCHAR": result = OracleType.LongVarChar; break;
                case "NCHAR": result = OracleType.NChar; break;
                case "NCLOB": result = OracleType.NClob; break;
                case "NUMBER": result = OracleType.Number; break;
                case "NVARCHAR": result = OracleType.NVarChar; break;
                case "RAW": result = OracleType.Raw; break;
                case "ROWID": result = OracleType.RowId; break;
                case "TIMESTAMP": result = OracleType.Timestamp; break;
                case "TIMESTAMPLOCAL": result = OracleType.TimestampLocal; break;
                case "TIMESTAMPWITHTZ": result = OracleType.TimestampWithTZ; break;
                case "VARCHAR": result = OracleType.VarChar; break;
                case "BYTE": result = OracleType.Byte; break;
                case "UINT16": result = OracleType.UInt16; break;
                case "UINT32": result = OracleType.UInt32; break;
                case "SBYTE": result = OracleType.SByte; break;
                case "INT16": result = OracleType.Int16; break;
                case "INT32": result = OracleType.Int32; break;
                case "FLOAT": result = OracleType.Float; break;
                case "DOUBLE": result = OracleType.Double; break;

            }
            return result;
        }
        private ParameterDirection GetParameterDirection(string pd)
        {
            ParameterDirection result = ParameterDirection.Input;
            switch (pd.ToUpper())
            {
                case "IN":
                    result = ParameterDirection.Input;
                    break;
                case "OUT":
                    result = ParameterDirection.Output;
                    break;
                case "INOUT":
                    result = ParameterDirection.InputOutput;
                    break;
                case "RETURN":
                    result = ParameterDirection.ReturnValue;
                    break;
            }
            return result;
        }
        
        /// <summary>
        /// 开启事务
        /// </summary>
        /// <returns></returns>
        public IDbTransaction BeginTran()
        {
            return this.Conn.BeginTransaction();
        }
        /// <summary>
        /// 开启连接
        /// </summary>
        public void Open()
        {
            this.Conn.Open();
        }
        /// <summary>
        /// 关闭连接
        /// </summary>
        public void Close()
        {
            this.Conn.Close();
        }

        private DynamicParameters GetParams(string SQL, IDictionary<string, object> data)
        {
            var param = new DynamicParameters();
            if (this._paramChar != "")
            {
                var mapList = Regex.Matches(SQL, @"\" + this._paramChar + @"\w+").Cast<Match>().Select(m => m.Value).Distinct().ToArray();

                foreach (KeyValuePair<string, object> prop in data)
                {
                    if (mapList.Contains(this._paramChar + prop.Key))
                    {
                        if (prop.Value != null)
                        {
                            if (prop.Value.GetType().Name.ToUpper() == "STRING")
                            {
                                string strValue = prop.Value.ToString();
                               int byteCount =  System.Text.Encoding.Default.GetByteCount(strValue);
                               if (byteCount > 4000)
                                {
                                    byte[] b ;
                                    if (IsBase64(prop.Value.ToString(), out b))
                                    {
                                        param.Add(prop.Key, b);
                                    }
                                    else
                                    {
                                        param.Add(prop.Key, new OracleClobParameter(prop.Value));
                                    }
                                }
                                else
                                {
                                    param.Add(prop.Key, new DbString() { Value = prop.Value.ToString(), IsAnsi = true });
                                }
                            }
                            else
                            {
                                param.Add(prop.Key, prop.Value);
                            }
                        }
                        else
                        {
                            param.Add(prop.Key, null);
                        }
                    }
                }
            }
            else
            {

                foreach (KeyValuePair<string, object> prop in data)
                {
                    if (prop.Value != null)
                    {
                        if (prop.Value.GetType().Name.ToUpper() == "STRING")
                        {
                            param.Add(prop.Key, new DbString() { Value = prop.Value.ToString(), IsAnsi = true });
                        }
                        else
                        {
                            param.Add(prop.Key, prop.Value.ToString());
                        }
                    }
                    else
                    {
                        param.Add(prop.Key, null);
                    }
                }
            }
            return param;
        }

        /// <summary>  
        /// 是否base64字符串  
        /// </summary>  
        /// <param name="base64Str">要判断的字符串</param>  
        /// <param name="bytes">字符串转换成的字节数组</param>  
        /// <returns></returns>  
        private bool IsBase64(string base64Str, out byte[] bytes)
        {
            //string strRegex = "^([A-Za-z0-9+/]{4})*([A-Za-z0-9+/]{4}|[A-Za-z0-9+/]{3}=|[A-Za-z0-9+/]{2}==)$";  
            bytes = null;
            if (string.IsNullOrEmpty(base64Str))
                return false;
            else
            {
                if (base64Str.Contains(","))
                    base64Str = base64Str.Split(',')[1];
                if (base64Str.Length % 4 != 0)
                    return false;
                if (base64Str.Any(c => !base64CodeArray.Contains(c)))
                    return false;
            }
            try
            {
                bytes = Convert.FromBase64String(base64Str);
                return true;
            }
            catch (FormatException)
            {
                return false;
            }
        }
        private static char[] base64CodeArray = new char[]  
        {  
            'A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z',  
            'a', 'b', 'c', 'd', 'e', 'f', 'g', 'h', 'i', 'j', 'k', 'l', 'm', 'n', 'o', 'p', 'q', 'r', 's', 't', 'u', 'v', 'w', 'x', 'y', 'z',  
            '0', '1', '2', '3', '4',  '5', '6', '7', '8', '9', '+', '/', '='  
        };  

        public IList<string> Match(string SQL, IDictionary<string, object> param)
        {
            IList<string> NoMatchList = new List<string>();
            if (this._paramChar != "")
            {
                var mapList = Regex.Matches(SQL, @"\" + this._paramChar + @"\w+").Cast<Match>().Select(m => m.Value).Distinct().ToArray();
                foreach (var p in mapList)
                {
                    string key = p.Replace(this._paramChar, "");
                    bool IsExist = false;
                    foreach (KeyValuePair<string, object> prop in param)
                    {
                        if (prop.Key == key)
                        {
                            IsExist = true;
                            break;
                        }
                    }
                    if (!IsExist)
                    {
                        NoMatchList.Add(key);
                    }
                }
            }
            return NoMatchList;
        }

        public void ChangeDatabase(string databaseName)
        {             
            //this.Conn.ChangeDatabase(databaseName);
            if (this._Conn == null)
            {
            }
            else
            {
                if (this._Conn.State == ConnectionState.Open)
                    this._Conn.Close();
            }
            this._db = new Microsoft.Practices.EnterpriseLibrary.Data.Oracle.OracleDatabase(databaseName);
            //同时将数据库链接制空，否则会影响使用 2020-2-6 by lins
            this._Conn = null;
        }
        #endregion
    }
}
